package db;

import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.Calendar;

/**
 * DBQuery class handles all querying interactions with the St.Patrick database
 * @author Gabrielle DuBois
 */
public class DBQuery extends DB{

    /**
     * @see db.DB
     */
    public DBQuery() throws Exception{
        super();
    }

    /**
     *
     * @param table a String whose options are: "families", "adults",
     *              "dependents", "ministries", "address", "money"
     * @param keyword a String containing the word to search
     * @return A ResultSet containing the query results
     */
    public ResultSet generalQuery(String table, String keyword){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;
        String word = keyword.toLowerCase();

        try{
            if (table.equalsIgnoreCase("families")){
                query = "SELECT * FROM "+table+" WHERE LOWER(familyName) LIKE ? " +
                        "OR LOWER(primaryPhone) LIKE ? " +
                        "OR LOWER(headOfHouse) LIKE ? " +
                        "OR LOWER(salutation) LIKE ? " +
                        "OR LOWER(notes) LIKE ? ORDER BY familyName";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1,"%"+word+"%");
                pstmt.setString(2,"%"+word+"%");
                pstmt.setString(3,"%"+word+"%");
                pstmt.setString(4,"%"+word+"%");
                pstmt.setString(5,"%"+word+"%");

                //serach database
                rs = pstmt.executeQuery();
                pstmt.clearParameters();

            }else if(table.equalsIgnoreCase("adults")){
                query = "SELECT * FROM "+table+" WHERE LOWER(firstName) LIKE ? " +
                        "OR LOWER(lastName) LIKE ? " +
                        "OR LOWER(dob) LIKE ? " +
                        "OR LOWER(dateJoined) LIKE ? " +
                        "OR LOWER(occupation) LIKE? " +
                        "OR LOWER(employer) LIKE ? " +
                        "OR LOWER(workPhone) LIKE ? " +
                        "OR LOWER(personalPhone) LIKE ? " +
                        "OR LOWER(notes) LIKE ? ORDER BY lastName";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1,"%"+word+"%");
                pstmt.setString(2,"%"+word+"%");
                pstmt.setString(3,"%"+word+"%");
                pstmt.setString(4,"%"+word+"%");
                pstmt.setString(5,"%"+word+"%");
                pstmt.setString(6,"%"+word+"%");
                pstmt.setString(7,"%"+word+"%");
                pstmt.setString(8,"%"+word+"%");
                pstmt.setString(9,"%"+word+"%");

                //serach database
                rs = pstmt.executeQuery();
                pstmt.clearParameters();

            }else if(table.equalsIgnoreCase("dependents")){
                query = "SELECT * FROM "+table+" WHERE LOWER(firstName) LIKE ? " +
                        "OR LOWER(lastName) LIKE ? " +
                        "OR LOWER(dob) LIKE ? " +
                        "OR LOWER(school) LIKE ? " +
                        "OR LOWER(religiousSchooling) LIKE ? " +
                        "OR LOWER(personalPhone) LIKE ? " +
                        "OR LOWER(notes) LIKE ? ORDER BY lastName";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1,"%"+word+"%");
                pstmt.setString(2,"%"+word+"%");
                pstmt.setString(3,"%"+word+"%");
                pstmt.setString(4,"%"+word+"%");
                pstmt.setString(5,"%"+word+"%");
                pstmt.setString(6,"%"+word+"%");
                pstmt.setString(7,"%"+word+"%");

                //serach database
                rs = pstmt.executeQuery();
                pstmt.clearParameters();

            }else if(table.equalsIgnoreCase("ministries")){
                query = "SELECT ministryName, isCommunityService, notes FROM "+table+" WHERE isActive=? AND " +
                        "( LOWER(ministryName) LIKE ? OR LOWER(notes) LIKE ?) ORDER BY ministryName";
                pstmt = conn.prepareStatement(query);
                pstmt.setBoolean(1, true);
                pstmt.setString(2,"%"+word+"%");
                pstmt.setString(3,"%"+word+"%");

                //serach database
                rs = pstmt.executeQuery();
                pstmt.clearParameters();
            }else if(table.equalsIgnoreCase("address")){
                query = "SELECT f.familyName, f.primaryPhone, a.street, cs.city, cs.state, " +
                        "a.zipcode, a.municipal, a.isCurrentMailing, a.notes " +
                        "FROM address a, city_state cs, fam_has_address f " +
                        "WHERE a.zipcode=cs.zipcode AND " +
                        " f.street=a.street AND (" +
                        "LOWER(f.familyName) LIKE ? " +
                        "OR LOWER(f.primaryPhone) LIKE ? " +
                        "OR LOWER(a.street) LIKE ? " +
                        "OR LOWER(cs.city) LIKE ? " +
                        "OR LOWER(cs.state) LIKE ? " +
                        "OR LOWER(a.zipcode) LIKE ? " +
                        "OR LOWER(a.municipal) LIKE ? " +
                        "OR LOWER(a.notes) LIKE ?) ORDER BY f.familyName";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1,"%"+word+"%");
                pstmt.setString(2,"%"+word+"%");
                pstmt.setString(3,"%"+word+"%");
                pstmt.setString(4,"%"+word+"%");
                pstmt.setString(5,"%"+word+"%");
                pstmt.setString(6,"%"+word+"%");
                pstmt.setString(7,"%"+word+"%");
                pstmt.setString(8,"%"+word+"%");

                //serach database
                rs = pstmt.executeQuery();
                pstmt.clearParameters();
            }else if(table.equalsIgnoreCase("money")){
                query = "SELECT familyName, primaryPhone, theYear, pledged, year2date, notes " +
                        "FROM " + table + " WHERE LOWER(familyName) LIKE ? " +
                        "OR LOWER(primaryPhone) LIKE ? " +
                        "OR LOWER(theYear) LIKE ? " +
                        //"OR pledged LIKE ? " +        //cant compare strings and doubles
                        //"OR year2date LIKE ? " +      //cant compare strings and doubles
                        "OR LOWER(notes) LIKE ? ORDER BY familyName";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1,"%"+word+"%");
                pstmt.setString(2,"%"+word+"%");
                pstmt.setString(3,"%"+word+"%");
                pstmt.setString(4,"%"+word+"%");
                //pstmt.setString(5,"%"+word+"%");
                //pstmt.setString(6,"%"+word+"%");

                //serach database
                rs = pstmt.executeQuery();
                pstmt.clearParameters();
            }
            conn.commit(); //clear locks
        }catch(Exception e){
            System.err.println("Error in DBManager.generalQuery(): " + e);
        }

        return rs;
    }//generalQuery()

    /**
     * Predefined Query 01. Salutation Preferred by a Particular Family
     * @param familyName
     * @param primaryPhone
     * @return A ResultSet
     */
    public ResultSet predefinedQuery01(String familyName, String primaryPhone){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT salutation FROM families WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();

        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery01(): " + e);
        }
        return rs;
    }//predefinedQuery01()

    /**
     * Predefined Query 02. A List of All Members of a Particular Family
     * @param familyName
     * @param primaryPhone
     * @return A ResultSet
     */
    public ResultSet predefinedQuery02(String familyName, String primaryPhone){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;
        try{
            query = "SELECT DISTINCT firstName, lastName, dob "
                    + "FROM family_composed_of "
                    + "WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();


        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery02(): " + e);
        }
        return rs;
    }//predefinedQuery02()

    /**
     * Predefined Query 03. Head of Household of a Particular Family
     * @param familyName
     * @param primaryPhone
     * @return A ResultSet containing the first name of the individual
     *          listed as the head of household for a particular family.
     */
    public ResultSet predefinedQuery03(String familyName, String primaryPhone){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT headOfHouse FROM families"
                    + " WHERE familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery03(): " + e);
        }
        return rs;
    }//predefinedQuery03()

    /**
     * Predefined Query 04. Partner of Head of Household of a Particular Family
     * @param familyName
     * @param primaryPhone
     * @return A ResultSet containing all the information of the individual
     *          listed as the partner of the head of household for a
     *          particular family. A null ResultSet means there was no Head of Household.
     */
    public ResultSet predefinedQuery04(String familyName, String primaryPhone){
        PreparedStatement pstmt;
        ResultSet rs = predefinedQuery03(familyName, primaryPhone);
        String query;

        try{
            if (rs.next()){
                String headHouse = rs.getString("headOfHouse");
                query = "SELECT DISTINCT f.firstName, f.lastName, f.dob "
                        + "FROM family_composed_of f, adults a "
                        + "WHERE f.firstName=a.firstName AND "
                        + "f.lastName=a.lastName AND f.dob=a.dob AND "
                        + "a.firstName <> ? AND familyName=? AND "
                        + "primaryPhone=?";
                pstmt = conn.prepareStatement(query);
                pstmt.setString(1, headHouse);
                pstmt.setString(2, familyName);
                pstmt.setString(3, primaryPhone);

                //query to database
                rs = pstmt.executeQuery();
            }else{
                rs = null;
            }
            
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery04(): " + e);
        }
        return rs;
    }//predefinedQuery04()

    /**
     * Predefined Query 05. A list of All Children of a Particular Family
     * @param familyName
     * @param primaryPhone
     * @return A ResultSet containing all the first name, last name and
     *          date of birth of all the children.
     */
    public ResultSet predefinedQuery05(String familyName, String primaryPhone){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT DISTINCT d.firstName, d.lastName, d.dob "
                    + "FROM family_composed_of f, dependents d "
                    + "WHERE f.firstName=d.firstName AND "
                    + "f.lastName=d.lastName AND f.dob=d.dob AND "
                    + "familyName=? AND primaryPhone=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery05(): " + e);
        }
        return rs;
    }//predefinedQuery05()

    /**
     * Predefined Query 06. A List of Families Associated with a Particular Child
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return A ResultSet containing...
     */
    public ResultSet predefinedQuery06(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT DISTINCT familyName, primaryPhone "
                    + "FROM family_composed_of  "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery06(): " + e);
        }
        return rs;
    }//predefinedQuery06()

    /**
     * Predefined Query 07. A List of Phone Numbers Belonging to a Particular Adult
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return A ResultSet containing...
     */
    public ResultSet predefinedQuery07(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT personalPhone, workPhone "
                    + "FROM adults "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery07(): " + e);
        }
        return rs;
    }//predefinedQuery07()

    /**
     * Predefined Query 08. A List of Phone Numbers Belonging to a Particular Dependent
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return A ResultSet containing...
     */
    public ResultSet predefinedQuery08(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT personalPhone "
                    + "FROM dependents "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery08(): " + e);
        }
        return rs;
    }//predefinedQuery08()

    /**
     * Predefined Query 09. A List of Individuals and Their Phone Numbers in Particular Family
     * @param familyName
     * @param primaryPhone
     * @return A ResultSet containing...
     */
    public ResultSet predefinedQuery09(String familyName, String primaryPhone){
        PreparedStatement pstmt;
        String query;
        ResultSet rs = null;

        try{
            query = "SELECT a.firstName, a.lastName, a.personalPhone, a.workPhone " +
                    "FROM adults a, family_composed_of f " +
                    "WHERE a.firstName=f.firstName AND a.lastName=f.lastName AND "
                    + "a.dob=f.dob AND familyName=? AND primaryPhone=? "
                    + "UNION "
                    + "SELECT d.firstName, d.lastName, personalPhone, workPhone "
                    + "FROM dependents d, family_composed_of f, emailworkphone "
                    + "WHERE d.firstName=f.firstName AND d.lastName=f.lastName AND "
                    + "d.dob=f.dob AND familyName=? AND primaryPhone=? "
                    + "ORDER BY lastName";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);
            pstmt.setString(3, familyName);
            pstmt.setString(4, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery09(): " + e);
        }
        return rs;
    }//predefinedQuery09()

    /**
     * Predefined Query 10. A List of Emails Belonging to a Particular Adult
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return A ResultSet containing...
     */
    public ResultSet predefinedQuery10(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT email "
                    + "FROM adults "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery10(): " + e);
        }
        return rs;
    }//predefinedQuery10()

    /**
     * Predefined Query 11. A List of Individuals and Their Emails in a Particular Family
     * @param familyName
     * @param primaryPhone
     * @return A ResultSet containing...
     */
    public ResultSet predefinedQuery11(String familyName, String primaryPhone){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT firstName, lastName, email " +
                    "FROM Adults " +
                    "WHERE firstName IN (SELECT firstName " +
                                        "FROM family_composed_of " +
					"WHERE familyName = (SELECT familyName " +
                                                            "FROM Families " +
                                                            "WHERE familyName=? AND primaryPhone=?)) AND "+
                            "lastName IN (SELECT lastName "+
                                         "FROM family_composed_of "+
					 "WHERE familyName = (SELECT familyName "+
                                                                "FROM Families "+
								"WHERE familyName=? AND primaryPhone=?)) AND "+
                            "dob IN (SELECT dob "+
                                    "FROM family_composed_of "+
                                    "WHERE familyName = (SELECT familyName "+
                                                        "FROM Families "+
							"WHERE familyName=? AND primaryPhone=?))";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, familyName);
            pstmt.setString(2, primaryPhone);
            pstmt.setString(3, familyName);
            pstmt.setString(4, primaryPhone);
            pstmt.setString(5, familyName);
            pstmt.setString(6, primaryPhone);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery11(): " + e);
        }
        return rs;
    }//predefinedQuery11()

    /**
     * Predefined Query 12. A List of all Families Whose Year-to-Date Contribution is Less Than the Pledged Amount
     * @param familyName
     * @param primaryPhone
     * @return
     */
    public ResultSet predefinedQuery12(){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;
        int currentYear = Calendar.getInstance().get(Calendar.YEAR);
        //May be off for an hour or two at new year but probably not an issue
        //int year (System.currentTimeMillis()/1000/3600/24/365.25 +1970);

        try{
            query = "SELECT theYear currentYear, familyName, primaryphone,"
                    + "year2date, pledged "
                    + "FROM money "
                    + "WHERE theYear=? AND year2date<pledged";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, currentYear+"");

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery12(): " + e);
        }
        return rs;
    }//predefinedQuery12()

    /**
     * Predefined Query 13. A List of Ministry Involvements of a Particular Adult
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return
     */
    public ResultSet predefinedQuery13(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT ministryName "
                    + "FROM aHistory "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery13(): " + e);
        }
        return rs;
    }//predefinedQuery13()

    /**
     * Predefined Query 14. A List of Ministry Involvements of a Particular Dependent
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return
     */
    public ResultSet predefinedQuery14(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT ministryName "
                    + "FROM dHistory "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery14(): " + e);
        }
        return rs;
    }//predefinedQuery14()

    /**
     * Predefined Query 15. The Date a Particular Dependent Last Helped With a Ministry
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return
     */
    public ResultSet predefinedQuery15(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT ministryName, date "
                    + "FROM dHistory "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery15(): " + e);
        }
        return rs;
    }//predefinedQuery15()

    /**
     * Predefined Query 16. The Date a Particular Adult Last Helped With a Ministry
     * @param firstName
     * @param lastName
     * @param dateOfBirth
     * @return
     */
    public ResultSet predefinedQuery16(String firstName, String lastName, String dateOfBirth){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT ministryName, date "
                    + "FROM aHistory "
                    + "WHERE firstName=? AND lastName=? AND dob=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, firstName);
            pstmt.setString(2, lastName);
            pstmt.setString(3, dateOfBirth);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery16(): " + e);
        }
        return rs;
    }//predefinedQuery16()

    /**
     * Predefined Query 17. A List of Names, Emails, Phone Numbers, and Mailing Addresses of all Who Indicated Interest in a Particular Ministry
     * @param ministryName
     * @return A ResultSet containing...
     */
    public ResultSet predefinedQuery17(String ministryName){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            //Union Adults and Dependents query for results
            query = "SELECT DISTINCT a.firstName, a.lastName, a.dob, a.email, "
                        + "fco.primaryPhone, a.personalPhone, a.workPhone, "
                        + "addr.street, cs.city, cs.state, cs.zipcode "
                    + "FROM aInterest ai, adults a, family_composed_of fco, "
                        + "fam_has_address f, address addr, city_state cs "
                    + "WHERE addr.zipcode = cs.zipcode AND "
                        + "addr.isCurrentMailing = true AND "
                        + "addr.street = f.street AND addr.zipcode = f.zipcode AND "
                        + "f.street = addr.street AND f.zipcode = addr.zipcode AND "
                        + "f.familyName = fco.familyName AND f.primaryPhone = fco.primaryPhone AND "
                        + "a.firstName = fco.firstName AND a.lastName = fco.lastName AND "
                        + "a.dob = fco.dob AND ai.firstName = a.firstName AND "
                        + "ai.ministryName=? "
                        + "UNION "
                        + "SELECT DISTINCT d.firstName, d.lastName, d.dob, email, "
                        + "fco.primaryPhone, d.personalPhone, workPhone, "
                        + "addr.street, cs.city, cs.state, cs.zipcode "
                    + "FROM dInterest di, dependents d, family_composed_of fco, "
                        + "fam_has_address f, address addr, city_state cs, emailworkphone "
                    + "WHERE addr.zipcode = cs.zipcode AND "
                        + "addr.isCurrentMailing = true AND "
                        + "addr.street = f.street AND addr.zipcode = f.zipcode AND "
                        + "f.street = addr.street AND f.zipcode = addr.zipcode AND "
                        + "f.familyName = fco.familyName AND f.primaryPhone = fco.primaryPhone AND "
                        + "d.firstName = fco.firstName AND d.lastName = fco.lastName AND "
                        + "d.dob = fco.dob AND di.firstName = d.firstName AND "
                        + "di.ministryName=?";
            pstmt = conn.prepareStatement(query);
            pstmt.setString(1, ministryName);
            pstmt.setString(2, ministryName);

            //query to database and add to arrayList
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery17(): " + e);
        }
        return rs;
    }//predefinedQuery17()

    /**
     * Predefined Query 18. A List of All Families with Dependents in Day School
     * @return
     */
    public ResultSet predefinedQuery18(){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT DISTINCT familyName, primaryPhone "
                    + "FROM family_composed_of f, dependents d "
                    + "WHERE f.firstName = d.firstName AND f.lastName = d.lastName AND "
                    + "f.dob = d.dob AND LOWER(religiousSchooling) = 'day school'";
            pstmt = conn.prepareStatement(query);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery18(): " + e);
        }
        return rs;
    }//predefinedQuery18()

    /**
     * Predefined Query 19. A List of All Families with Dependents in Religious Education
     * @return
     */
    public ResultSet predefinedQuery19(){
        PreparedStatement pstmt;
        ResultSet rs = null;
        String query;

        try{
            query = "SELECT DISTINCT familyName, primaryPhone "
                    + "FROM family_composed_of f, dependents d "
                    + "WHERE f.firstName = d.firstName AND f.lastName = d.lastName AND "
                    + "f.dob = d.dob AND LOWER(religiousSchooling) = 'religious education'";
            pstmt = conn.prepareStatement(query);

            //query to database
            rs = pstmt.executeQuery();
            conn.commit(); //clear locks
        }catch(SQLException e){
            System.err.println("Error in DBManager.predefinedQuery19(): " + e);
        }
        return rs;
    }//predefinedQuery19()

}//DBManager
